﻿Public Class gSQLite
    Public Shared Function gDataTable(ByVal fPath As String, ByVal pwd As String, ByVal sql As String) As Data.DataTable
        Dim pdb As IntPtr, dt As New Data.DataTable
        sqlite3.sqlite3_open16(fPath, pdb)
        If pwd.Length > 0 Then
            sqlite3.sqlite3_key(pdb, pwd, pwd.Length)
        End If
        Dim sqls As Byte() = System.Text.Encoding.UTF8.GetBytes(sql), stmt As IntPtr, str() As String
        Dim err As Integer = sqlite3.sqlite3_prepare_v2(pdb, sqls, sqls.Length, stmt, 0)
        If err = 0 Then
            Dim col As Integer = sqlite3.sqlite3_column_count(stmt)
            Dim gcol As Boolean = True '首次添加列名
            ReDim str(col - 1)
            Do
                Dim ret As Integer = sqlite3.sqlite3_step(stmt)
                If ret = sqlite3.SQLITE_ROW Then
                    For i = 0 To col - 1
                        Select Case sqlite3.sqlite3_column_type(stmt, i)
                            Case sqlite3.SQLITE_INTEGER '处理整型
                                If gcol Then
                                    dt.Columns.Add(Runtime.InteropServices.Marshal.PtrToStringUni(sqlite3.sqlite3_column_origin_name16(stmt, i)), Type.GetType("System.Int64"))
                                End If
                                str(i) = sqlite3.sqlite3_column_int(stmt, i)
                            Case sqlite3.SQLITE_FLOAT '处理浮点数
                                If gcol Then
                                    dt.Columns.Add(Runtime.InteropServices.Marshal.PtrToStringUni(sqlite3.sqlite3_column_origin_name16(stmt, i)), Type.GetType("System.Double"))
                                End If
                                str(i) = sqlite3.sqlite3_column_double(stmt, i)
                            Case sqlite3.SQLITE_TEXT '处理字符串
                                If gcol Then
                                    dt.Columns.Add(Runtime.InteropServices.Marshal.PtrToStringUni(sqlite3.sqlite3_column_origin_name16(stmt, i)), Type.GetType("System.String"))
                                End If
                                str(i) = Runtime.InteropServices.Marshal.PtrToStringUni(sqlite3.sqlite3_column_text16(stmt, i))
                            Case sqlite3.SQLITE_BLOB '处理二进制数据
                                If gcol Then
                                    dt.Columns.Add(Runtime.InteropServices.Marshal.PtrToStringUni(sqlite3.sqlite3_column_origin_name16(stmt, i)))
                                End If
                                str(i) = Nothing '尚未实现
                            'sqlite3.sqlite3_column_blob(stmt, i)
                            Case sqlite3.SQLITE_NULL '处理空值
                                If gcol Then
                                    dt.Columns.Add(Runtime.InteropServices.Marshal.PtrToStringUni(sqlite3.sqlite3_column_origin_name16(stmt, i)))
                                End If
                                str(i) = Nothing
                            Case sqlite3.SQLITE_DONE
                                Exit Do
                        End Select
                    Next
                    gcol = False
                    dt.Rows.Add(str)
                Else
                    Exit Do
                End If
            Loop While True
        Else
            dt.Columns.Add("执行错误")
            Dim errmsg As String = sqlite3.sqlite3_errmsg(pdb)
            Select Case err
                Case sqlite3.SQLITE_NOTADB
                    errmsg = "文件被加密或不是有效的数据库"
            End Select
            dt.Rows.Add({errmsg})
        End If
        sqlite3.sqlite3_close(pdb)
        Return dt
    End Function
    Public Shared Sub gInsert(ByVal fPath As String, ByVal pwd As String, ByVal sql As String)
        Dim pdb As IntPtr
        sqlite3.sqlite3_open16(fPath, pdb)
        If pwd.Length > 0 Then
            sqlite3.sqlite3_key(pdb, pwd, pwd.Length)
        End If
        sqlite3.sqlite3_exec(pdb, System.Text.Encoding.UTF8.GetBytes("begin;"), 0, 0, 0)
        sqlite3.sqlite3_exec(pdb, System.Text.Encoding.UTF8.GetBytes(sql), 0, 0, 0)
        sqlite3.sqlite3_exec(pdb, System.Text.Encoding.UTF8.GetBytes("commit;"), 0, 0, 0)
        sqlite3.sqlite3_close(pdb)
    End Sub
    Public Shared Sub ClearPwd(ByVal fPath As String, ByVal pwd As String)
        Dim pdb As IntPtr
        sqlite3.sqlite3_open16(fPath, pdb)
        If pwd.Length > 0 Then
            sqlite3.sqlite3_key(pdb, pwd, pwd.Length)
            sqlite3.sqlite3_rekey(pdb, vbNullString, 0)
        End If
        sqlite3.sqlite3_close(pdb)
    End Sub
    Public Shared Sub AddPwd(ByVal fPath As String, ByVal pwd As String)
        Dim pdb As IntPtr
        sqlite3.sqlite3_open16(fPath, pdb)
        If pwd.Length > 0 Then
            sqlite3.sqlite3_rekey(pdb, pwd, pwd.Length)
        End If
        sqlite3.sqlite3_close(pdb)
    End Sub
End Class